package www.worq.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import www.worq.controller.beans.tramiteBean;

public class tramitesDao extends oreqDao {

	public ArrayList<tramiteBean> getTRAMITES() throws SQLException {
		ArrayList<tramiteBean> cat_tramites = new ArrayList<tramiteBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from cat_tramite");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.tramites.getTRAMITES.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				tramiteBean tramite = new tramiteBean();
				tramite.setId_tramite(rs.getInt(1));
				tramite.setCategoria(rs.getString(2));
				tramite.setNombre(rs.getString(3));
				tramite.setDescripcion(rs.getString(4));
				tramite.setCosto(rs.getDouble(5));
				tramite.setTiempo_respuesta(rs.getInt(6));
				tramite.setDoc_esperado(rs.getString(7));
				tramite.setHorarios(rs.getString(8));
				tramite.setObservaciones(rs.getString(9));
				tramite.setFech_actualizacion(rs.getString(10));
				tramite.setRequisitos(rs.getString(11));
				cat_tramites.add(tramite);
			}
			System.out.println("dao.tramites.getTRAMITES----------> Cargado");
		} catch (Exception e) {
			System.out.println("tramitesDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return cat_tramites;
	}

	public ArrayList<tramiteBean> getTRAMITE(int id) throws SQLException {
		ArrayList<tramiteBean> cat_tramites = new ArrayList<tramiteBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from cat_tramite where id_tramite = ")
					.append(id);
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.tramites.getTRAMITE.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				tramiteBean tramite = new tramiteBean();
				tramite.setId_tramite(rs.getInt(1));
				tramite.setCategoria(rs.getString(2));
				tramite.setNombre(rs.getString(3));
				tramite.setDescripcion(rs.getString(4));
				tramite.setCosto(rs.getDouble(5));
				tramite.setTiempo_respuesta(rs.getInt(6));
				tramite.setDoc_esperado(rs.getString(7));
				tramite.setHorarios(rs.getString(8));
				tramite.setObservaciones(rs.getString(9));
				tramite.setFech_actualizacion(rs.getString(10));
				tramite.setRequisitos(rs.getString(11));
				cat_tramites.add(tramite);
			}
			System.out.println("dao.tramites.getTRAMITE----------> Cargado");
		} catch (Exception e) {
			System.out.println("tramitesDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return cat_tramites;
	}

	public double getCOSTO(String tramite) throws SQLException {
		double costo = 0.0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select costo from cat_tramite where nombre ='")
					.append(tramite).append("'");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.tramites.getCOSTO.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				costo = (rs.getDouble(1));
			}
			System.out.println("dao.tramites.getCOSTO----------> Cargado");
		} catch (Exception e) {
			System.out.println("tramitesDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return costo;
	}

	public int nextId() {
		int id = 0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select id_tramite from cat_tramite");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.tramites.nextId.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				if (id < rs.getInt(1)) {
					id = rs.getInt(1);
				}
			}
			System.out.println("dao.tramites.nextId----------> Cargado");
		} catch (Exception e) {
			System.out.println("tramitesDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return id + 1;
	}

	public void insertTRAMITE(tramiteBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("INSERT INTO cat_tramite ")
					.append("(id_tramite, categoria, nombre, descripcion, costo, tiempo_respuesta, doc_esperado, horarios, observaciones, fech_actualizacion, requisitos) ")
					.append("values ('").append(bean.getId_tramite())
					.append("','").append(bean.getCategoria()).append("','")
					.append(bean.getNombre()).append("','")
					.append(bean.getDescripcion()).append("','")
					.append(bean.getCosto()).append("','")
					.append(bean.getTiempo_respuesta()).append("','")
					.append(bean.getDoc_esperado()).append("','")
					.append(bean.getHorarios()).append("','")
					.append(bean.getObservaciones()).append("','")
					.append(bean.getFech_actualizacion()).append("','")
					.append(bean.getRequisitos()).append("');");
			System.out
					.println("dao.tramites.insertTRAMITE.Ejecutado----------> "
							+ query.toString());
			st = conn.createStatement();
			st.executeUpdate(query.toString());

			System.out
					.println("dao.tramites.insertUsuario----------> insertado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void deleteTRAMITE(tramiteBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("DELETE FROM cat_tramite where id_tramite=").append(
					bean.getId_tramite());

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.tramites.deleteTRAMITE.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.tramites.deleteTRAMITE----------> eliminado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updateTRAMITE(tramiteBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE cat_tramite SET ").append("categoria='")
					.append(bean.getCategoria()).append("',nombre='")
					.append(bean.getNombre()).append("',descripcion='")
					.append(bean.getDescripcion()).append("',costo='")
					.append(bean.getCosto()).append("',tiempo_respuesta='")
					.append(bean.getTiempo_respuesta())
					.append("',doc_esperado='").append(bean.getDoc_esperado())
					.append("',horarios='").append(bean.getHorarios())
					.append("',observaciones='")
					.append(bean.getObservaciones())
					.append("',fech_actualizacion='")
					.append(bean.getFech_actualizacion())
					.append("',requisitos='").append(bean.getRequisitos())
					.append("' where id_tramite=").append(bean.getId_tramite())
					.append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.tramites.updateTRAMITE.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.tramites.updateTRAMITE----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

}
